﻿package com.huasoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.huasoft.domain.File;
import com.huasoft.domain.User;
import com.huasoft.domain.UserFile;
import com.huasoft.util.JDBConnection;

/**
 * 用户借阅表的相应行为
 * 
 * @author Administrator
 * 
 */
public class UserFileDao {

	public boolean save(User user, User managerUser, File file) {
		String sql = "insert into userfile values(null,?,?,?,?)";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, user.getId());
			ps.setInt(2, file.getId());
			ps.setInt(3, managerUser.getId());
			if (user.getId() == managerUser.getId()) {
				ps.setString(4, "同意");
			} else {
				ps.setString(4, "审核中");
			}
			int rowNumber = ps.executeUpdate();
			if (rowNumber > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public List<UserFile> listUserFile(String status,
			PageCondition pageCondition, User manageruser) {
		StringBuffer sql = new StringBuffer(
				"select uf.id,f.types,f.title,u.name,uf.give_status"
						+ " from userfile uf,users u,files f"
						+ " where uf.manager_id=" + manageruser.getId()
						+ " and uf.file_id=f.id and u.id=uf.user_id");
		Connection con = null;
		Statement st = null;
		List<UserFile> files = new ArrayList<UserFile>();
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if ("审核中".equals(status)) {
				sql.append(" and give_status = '" + status + "'");
			} else if ("已审核".equals(status)) {
				sql.append(" and give_status != '" + "审核中" + "'");
			}
			System.out.println(sql.toString());
			int begin = (pageCondition.getPage() - 1)
					* pageCondition.getPageSize();
			System.out.println("begin" + begin);
			int end = pageCondition.getPageSize();
			System.out.println("end" + end);
			sql.append(" limit " + begin + "," + end);
			System.out.println(sql);
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			while (rs.next()) {
				UserFile userfile = new UserFile();
				userfile.setId(rs.getInt(1));
				userfile.setFiletype(rs.getString(2));
				userfile.setFilename(rs.getString(3));
				userfile.setUsername(rs.getString(4));
				userfile.setGiveStatus(rs.getString(5));
				files.add(userfile);
			}
			System.out.println(files.size() + "---------");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		System.out.println(files.size() + "abx");
		return files;
	}

	public void totalPage(String status, PageCondition pageCondition,
			User manageruser) {
		StringBuffer sql = new StringBuffer("select count(*)"
				+ " from userfile uf,users u,files f" + " where uf.manager_id="
				+ manageruser.getId()
				+ " and uf.file_id=f.id and u.id=uf.user_id");
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if ("审核中".equals(status)) {
				sql.append(" and give_status = '" + status + "'");
			} else if ("已审核".equals(status)) {
				sql.append(" and give_status != '" + "审核中" + "'");
			}
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			rs.next();
			int num = rs.getInt(1);
			if (num % pageCondition.getPageSize() == 0) {
				pageCondition.setTotalPage(num / pageCondition.getPageSize());
			} else {
				pageCondition.setTotalPage(num / pageCondition.getPageSize()
						+ 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public boolean checkedFile(UserFile userfile) {
		String sql = "update userfile set give_status='"
				+ userfile.getGiveStatus() + "' where id=" + userfile.getId()
				+ "";
		System.out.println(sql);
		Connection con = null;
		Statement st = null;
		try {
			con = JDBConnection.getConnection();
			st = con.createStatement();
			int rowNum = st.executeUpdate(sql);
			if (rowNum > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, null);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public Integer getFileIdById(UserFile userfile) {
		String sql = "select file_id from userfile where id="
				+ userfile.getId() + "";
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return null;
	}

	public List<UserFile> listUserFileByUserId(String status,
			PageCondition pageCondition, User user) {
		StringBuffer sql = new StringBuffer(
				"select uf.id,f.types,f.title,uf.give_status"
						+ " from userfile uf,files f" + " where uf.user_id="
						+ user.getId() + " and uf.file_id=f.id");
		Connection con = null;
		Statement st = null;
		List<UserFile> files = new ArrayList<UserFile>();
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if ("已归还".equals(status)) {
				sql.append(" and give_status = '" + status + "'");
			} else if ("未归还".equals(status)) {
				sql.append(" and give_status = '" + "同意" + "'");
			}
			System.out.println(sql.toString());
			int begin = (pageCondition.getPage() - 1)
					* pageCondition.getPageSize();
			System.out.println("begin" + begin);
			int end = pageCondition.getPageSize();
			System.out.println("end" + end);
			sql.append(" limit " + begin + "," + end);
			System.out.println(sql);
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			while (rs.next()) {
				UserFile userfile = new UserFile();
				userfile.setId(rs.getInt(1));
				userfile.setFiletype(rs.getString(2));
				userfile.setFilename(rs.getString(3));
				userfile.setGiveStatus(rs.getString(4));
				files.add(userfile);
			}
			System.out.println(files.size() + "---------");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		System.out.println(files.size() + "abx");
		return files;
	}

	public void totalPageByUserId(String status, PageCondition pageCondition,
			User user) {
		StringBuffer sql = new StringBuffer("select count(*)"
				+ " from userfile uf,files f" + " where uf.user_id="
				+ user.getId() + " and uf.file_id=f.id");
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if ("已归还".equals(status)) {
				sql.append(" and give_status = '" + status + "'");
			} else if ("未归还".equals(status)) {
				sql.append(" and give_status != '" + "同意" + "'");
			}
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			rs.next();
			int num = rs.getInt(1);
			if (num % pageCondition.getPageSize() == 0) {
				pageCondition.setTotalPage(num / pageCondition.getPageSize());
			} else {
				pageCondition.setTotalPage(num / pageCondition.getPageSize()
						+ 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
}
